package dao.community;


import dao.BaseDao;
import domain.Community;
import domain.Page;
import util.DButils;
import util.StringUtil;


import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

public class CommunityDaoImpl implements CommunityDao {


    /**
     * 通过community对象增加社区信息
     *
     * @param connection
     * @param community
     * @return domain.Community
     * @author 荣燊
     * @date 2020/7/14 10:54
     */
    @Override
    public int addCommunity(Connection connection, Community community) throws Exception {
        PreparedStatement pstm = null;
        int flag = 0;
        if(null != connection){
            String sql = "insert into community (Community_id,Community_name,Community_info) " +
                    "values(?,?,?)";
            Object[] params = {community.getCommunity_id(),community.getCommunity_name(),
                    community.getCommunity_info()};
            System.out.println("sql增加语句是------------>"+sql);
            flag = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return flag;
    }

    /**
     * 根据community_id删除社区信息
     *
     * @param connection
     * @param community_id
     * @retur int
     * @author 荣燊
     * @date 2020/7/12 21:55
     */
    @Override
    public int delteCommunity(Connection connection, int community_id) throws Exception {
        PreparedStatement pstm = null;
        int flag = 0;
        if(null != connection){
            String sql = "delete from community where Community_id=?";
            Object[] params = {community_id};
            flag = BaseDao.execute(connection, pstm, sql, params);
            System.out.println("sql的删除语句是:"+sql);
            BaseDao.closeResource(null, pstm, null);
        }
        return flag;
    }

    /**
     * 根据community_id修改社区信息
     * @param connection
     * @param community_id
     * @param community_info
     * @return int
     */
    @Override
    public int modifyCommunity(Connection connection, int community_id, String community_info) throws Exception {
        int flag = 0;
        PreparedStatement pstm = null;
        if(null != connection){
            String sql = "update community set Community_info=?"+
                    " where Community_id=?";
            Object[] params = {community_info, community_id};
            flag = BaseDao.execute(connection, pstm, sql, params);
            System.out.println("修改的sql语句是——————————————》"+sql);
            BaseDao.closeResource(null, pstm, null);
        }
        return flag;
    }

    /**
     * 查询获取所有社区信息
     *
     * @param connection
     * @return java.util.List<domain.Community>
     * @author 夏天辣鸡
     * @date 2020/7/12 22:09
     */
    @Override
    public List<Community> getCommunityList(Connection connection, Page page) throws Exception {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Community community = null;
        List<Community> list = new ArrayList<>();
        if (connection != null){
            String sql = "select * from community limit " + page.getBeginIndex() + ", " + page.getEveryPage();
            Object[] params = {};
            System.out.println("sql查询语句是------------>"+sql);
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()){
                community = new Community();
                community.setCommunity_id(rs.getInt("Community_id"));
                community.setCommunity_info(rs.getString("Community_info"));
                community.setCommunity_name(rs.getString("Community_name"));
                list.add(community);
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return list;
    }

    /**
     * 这个无参也是夏天写的，查询所有社区信息
     * @param
     * @return java.util.List<domain.Community>
     * @author 荣燊
     * @date 2020/7/15 11:07
     */
    @Override
    public List<Community> getCommunityList() throws Exception{
        Connection connection =  DButils.getConn();
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Community community = null;
        List<Community> list = new ArrayList<>();
        if (connection != null){
            String sql = "select * from community ";
            Object[] params = {};
            System.out.println("sql语句是------------>"+sql);
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()){
                community = new Community();
                community.setCommunity_id(rs.getInt("Community_id"));
                community.setCommunity_info(rs.getString("Community_info"));
                community.setCommunity_name(rs.getString("Community_name"));
                list.add(community);
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return list;
    }

    /**
     * 根据条件查询社区信息
     * @param connection
     * @return java.util.List<domain.Community>
     * @author 荣燊
     * @date 2020/7/15 10:51
     */
    @Override
    public List<Community> getCommunityList(Connection connection) throws Exception{
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Community community = null;
        List<Community> list = new ArrayList<>();
        if (connection != null){
            String sql = "select * from community ";
            Object[] params = {};
            System.out.println("sql语句是------------>"+sql);
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            while (rs.next()){
                community = new Community();
                community.setCommunity_id(rs.getInt("Community_id"));
                community.setCommunity_info(rs.getString("Community_info"));
                community.setCommunity_name(rs.getString("Community_name"));
                list.add(community);
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return list;
    }


    /**
     * 根据社区id查询信息
     *
     * @param connection
     * @param community_id
     * @return
     * @author 荣燊
     */
    @Override
    public boolean getCommunityList(Connection connection, int community_id) throws Exception {
        boolean flag = false;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Community community = null;
        List<Community> list = new ArrayList<>();
        if (connection != null){
            String sql = "select * from room where Community_id = ? ";
            Object[] params = {community_id};
            System.out.println("sql语句是------------>"+sql);
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            if (rs.last()){
                flag = true;
            }
            BaseDao.closeResource(null,pstm,rs);
        }
//        System.out.println("community dao层的flag为"+flag);
        return flag;
    }

    /**
     * 根据viewPower查询社区信息，并拼接为字符串
     * @param view_power
     * @return java.util.List<domain.Community>
     * @author 夏天
     * @date 2020/7/15 12:26
     */
    @Override
    public String getIdForName(String view_power) throws Exception{
        String sql = "select Community_name from community where Community_id in(";
        StringUtil su = new StringUtil();
        String[] Community_id = su.fengeStr(view_power,",");
        for (int i=0;i<Community_id.length;i++) {
            sql += Community_id[i];
            if(i<Community_id.length-1)
                sql+=",";
        }
        sql += ")";
        String CommunityName="";
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()){
            CommunityName += rs.getString("Community_name")+",";

        }
        DButils.close(rs,st,con);
        return CommunityName;
    }

    /**
     * 根据community_id删除社区信息
     *
     * @return int
     * @author 夏添
     * @date 2020/7/14 9:15
     */
    @Override
    public int getCont() {  //获取社区数量
        String sql = "select count(*) as count from community ";
        Connection con = DButils.getConn();
        int count = 0;
        try {
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql);
            rs.next();
            count = rs.getInt("count");
            DButils.close(rs, st, con);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
    /**
     * 条件筛选社区
     * @param  view_power
     * @return ArrayList
     * @author 夏天
     * @date 2020/7/21 20:32
     */
    @Override
    public ArrayList<Community> queryCommunity(String view_power) throws Exception{
        view_power = view_power.substring(0,view_power.length()-1);
        String sql = "select * from community where Community_id in ("+view_power+")";
        Connection con = DButils.getConn();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        ArrayList<Community> list =new ArrayList<>();
        while (rs.next()){
            Community community = new Community();
            community.setCommunity_id(rs.getInt("Community_id"));
            community.setCommunity_name(rs.getString("Community_name"));
            community.setCommunity_info(rs.getString("Community_info"));
            list.add(community);
        }
        return list;
    }
}
